#!/bin/bash
APP=education

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi

dwd_trade_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition (dt)
select
    id,
    user_id,
    course_id,
    course_name,
    date_format(create_time, 'yyyy-MM-dd') date_id,
    cart_price,
    session_id,
    create_time,
    update_time,
    deleted,
    sold,
    date_format(create_time, 'yyyy-MM-dd') dt
from
(
     select
        data.id,
        data.user_id,
        data.course_id,
        data.course_name,
        data.cart_price,
        data.session_id,
        data.create_time,
        data.update_time,
        data.deleted,
        data.sold
    from ${APP}.ods_cart_info_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
)t1;
"

dwd_trade_order_detail_inc="
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition (dt)
select
    od.id,
    course_id,
    course_name,
    order_id,
    user_id,
    origin_amount,
    coupon_reduce,
    final_amount,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    update_time,
    session_id,
    province_id,
    order_status,
    out_trade_no,
    date_format(create_time,'yyyy-MM-dd') dt
from
(
    select
        data.id,
        data.course_id,
        data.course_name,
        data.order_id,
        data.user_id,
        data.origin_amount,
        data.coupon_reduce,
        data.final_amount,
        data.create_time,
        data.update_time
    from ${APP}.ods_order_detail_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
) od left join
(
     select
        data.id,
        data.session_id,
        data.province_id,
        data.order_status,
        data.out_trade_no
    from
    ${APP}.ods_order_info_inc
)info on od.order_id=info.id;
"

dwd_trade_pay_detail_suc_inc="
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition (dt)
select
        pay.id,
        out_trade_no,
        pay.order_id,
        alipay_trade_no,
        total_amount,
        payment_type,
        payment_status,
        date_format(pay.create_time,'yyyy-MM-dd') date_id,
        pay.create_time,
        update_time,
        callback_content,
        callback_time,
        user_id,
        origin_amount,
        coupon_reduce,
        final_amount,
        course_id,
        course_name,
        date_format(pay.create_time,'yyyy-MM-dd')
from
(
    select
        data.id,
        data.out_trade_no,
        data.order_id,
        data.alipay_trade_no,
        data.total_amount,
        data.payment_type,
        data.payment_status,
        data.create_time,
        data.update_time,
        data.callback_content,
        data.callback_time
    from ${APP}.ods_payment_info_inc
    where dt = '$do_date'
    and type = 'bootstrap-insert'
    and  data.payment_status =1602
) pay join
(
    select
        data.id,
        data.course_id,
        data.course_name,
        data.user_id,
        data.order_id,
        data.origin_amount,
        data.coupon_reduce,
        data.final_amount,
        data.create_time
    from ${APP}.ods_order_detail_inc
        where dt = '$do_date'
        and type = 'bootstrap-insert'
) ord 
on pay.order_id=ord.order_id;
"

dwd_traffic_page_view_inc="
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    source,
    operate_system,
    user_id,
    version_code,
    session_id,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_type,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    during_time
from
(
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.sc source,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        common.sid session_id,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        page.source_type,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and page.page_id is not null
)log;
"

dwd_traffic_display_inc="
insert overwrite table ${APP}.dwd_traffic_display_inc partition (dt = '$do_date')
select province_id,
       brand,
       channel,
       is_new,
       model,
       mid_id,
       sc,
       sid,
       operate_system,
       user_id,
       version_code,
       during_time,
       page_item,
       page_item_type,
       last_page_id,
       page_id,
       source_type,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd')          date_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') display_time,
       display_type,
       display_item,
       display_item_type,
       display_order,
       display_pos_id
from 
(
     select common.ar         area_code,
            common.ba         brand,
            common.ch         channel,
            common.is_new,
            common.md         model,
            common.mid        mid_id,
            common.sc         sc,
            common.sid        sid,
            common.os         operate_system,
            common.uid        user_id,
            common.vc         version_code,
            page.during_time,
            page.item         page_item,
            page.item_type    page_item_type,
            page.last_page_id,
            page.page_id,
            page.source_type,
            display.display_type,
            display.item      display_item,
            display.item_type display_item_type,
            display.`order`   display_order,
            display.pos_id    display_pos_id,
            ts
     from ${APP}.ods_log_inc 
     lateral view explode(displays) tmp as display
     where dt = '$do_date'
       and displays is not null
 ) log
     left join
 (
     select id province_id,
            area_code
     from ${APP}.ods_base_province_full
     where dt = '$do_date'
 ) bp
 on log.area_code = bp.area_code;
"

dwd_traffic_action_inc="
insert overwrite table ${APP}.dwd_traffic_action_inc partition (dt = '$do_date')
select province_id,
       brand,
       channel,
       is_new,
       model,
       mid_id,
       sc,
       sid,
       operate_system,
       user_id,
       version_code,
       during_time,
       page_item,
       page_item_type,
       last_page_id,
       page_id,
       source_type,
       play_sec,
       position_sec,
       video_id,
       action_item,
       action_item_type,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd')          date_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') action_time
from 
(
     select common.ar             area_code,
            common.ba             brand,
            common.ch             channel,
            common.is_new,
            common.md             model,
            common.mid            mid_id,
            common.sc             sc,
            common.sid            sid,
            common.os             operate_system,
            common.uid            user_id,
            common.vc             version_code,
            page.during_time,
            page.item             page_item,
            page.item_type        page_item_type,
            page.last_page_id,
            appVideo.play_sec     play_sec,
            appVideo.position_sec position_sec,
            appVideo.video_id     video_id,
            page.page_id,
            page.source_type,
            action.action_id,
            action.item           action_item,
            action.item_type      action_item_type,
            action.ts
     from ${APP}.ods_log_inc 
     lateral view explode(actions) tmp as action
     where dt = '$do_date'
       and actions is not null
 ) log
     left join
 (
     select id province_id,
            area_code
     from ${APP}.ods_base_province_full
     where dt = '$do_date'
 ) bp
 on log.area_code = bp.area_code;
"


dwd_traffic_start_inc="
insert overwrite table ${APP}.dwd_traffic_start_inc partition (dt = '$do_date')
select province_id,
       brand,
       channel,
       is_new,
       model,
       mid_id,
       operate_system,
       sc,
       sid,
       user_id,
       version_code,
       entry,
       first_open,
       open_ad_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd')          date_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') start_time,
       loading_time,
       open_ad_ms,
       open_ad_skip_ms,
       play_sec,
       position_sec,
       video_id
from 
(
     select common.ar             area_code,
            common.ba             brand,
            common.ch             channel,
            common.is_new,
            common.md             model,
            common.mid            mid_id,
            common.os             operate_system,
            common.sc             sc,
            common.sid            sid,
            common.uid            user_id,
            common.vc             version_code,
            `start`.entry,
            `start`.first_open    first_open,
            `start`.loading_time,
            `start`.open_ad_id,
            `start`.open_ad_ms,
            `start`.open_ad_skip_ms,
            appVideo.play_sec     play_sec,
            appVideo.position_sec position_sec,
            appVideo.video_id     video_id,
            ts
     from ${APP}.ods_log_inc
     where dt = '$do_date'
       and `start` is not null
 ) log
     left join
 (
     select id province_id,
            area_code
     from ${APP}.ods_base_province_full
     where dt = '$do_date'
 ) bp
 on log.area_code = bp.area_code;
"


dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition (dt = '$do_date')
select user_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd')          date_id,
       date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') login_time,
       channel,
       province_id,
       version_code,
       mid_id,
       brand,
       model,
       operate_system
from 
(
     select user_id,
            channel,
            area_code,
            version_code,
            mid_id,
            brand,
            model,
            operate_system,
            ts
     from 
     (
          select user_id,
                 channel,
                 area_code,
                 version_code,
                 mid_id,
                 brand,
                 model,
                 operate_system,
                 ts,
                 row_number() over (partition by session_id order by ts) rn
          from 
          (
               select user_id,
                      channel,
                      area_code,
                      version_code,
                      mid_id,
                      brand,
                      model,
                      operate_system,
                      ts,
                      concat(mid_id, '-', last_value(session_start_point, true)
                                                     over (partition by mid_id order by ts)) session_id
               from 
               (
                    select common.uid                              user_id,
                           common.ch                               channel,
                           common.ar                               area_code,
                           common.vc                               version_code,
                           common.mid                              mid_id,
                           common.ba                               brand,
                           common.md                               model,
                           common.os                               operate_system,
                           ts,
                           if(page.last_page_id is null, ts, null) session_start_point
                    from ${APP}.ods_log_inc
                    where dt = '$do_date'
                      and page is not null
                ) t1
           ) t2
          where user_id is not null
        ) t3
     where rn = 1
 ) t4
     left join
 (
     select id province_id,
            area_code
     from ${APP}.ods_base_province_full
     where dt = '$do_date'
 ) bp
 on t4.area_code = bp.area_code;
"


dwd_traffic_error_inc="
insert overwrite table ${APP}.dwd_traffic_error_inc partition(dt='$do_date')
select
    common.ar province_id,
    common.ba brand,
    common.ch channel,
    common.is_new,
    common.md model,
    common.mid mid_id,
    common.os operate_system,
    common.sc source,
    common.sid sid_id,
    common.uid user_id,
    common.vc version_code,
    page.item page_item,
    page.item_type page_item_type,
    page.last_page_id,
    page.page_id,
    page.source_type,
    `start`.entry,
    `start`.first_open,
    `start`.loading_time,
    `start`.open_ad_id,
    `start`.open_ad_ms,
    `start`.open_ad_skip_ms,
    appVideo.play_sec,
    appVideo.position_sec,
    appVideo.video_id,
    actions,
    displays,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') error_time,
    err.error_code,
    err.msg error_msg
from ${APP}.ods_log_inc
where dt ='$do_date'
and err is not null;
"

dwd_user_register_inc="
insert overwrite table ${APP}.dwd_user_register_inc partition (dt)
select
    ui.user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system,
    date_format(create_time,'yyyy-MM-dd')
from
(
    select
        data.id user_id,
        data.create_time
    from ${APP}.ods_user_info_inc
    where dt='$do_date'
    and type='bootstrap-insert'
)ui
left join
(
    select
        user_id,
        collect_set(province_id)[0] province_id,
        collect_set(brand)[0] brand,
        collect_set(channel)[0] channel,
        collect_set(model)[0] model,
        collect_set(mid_id)[0] mid_id,
        collect_set(operate_system)[0] operate_system,
        collect_set(version_code)[0] version_code
    from
    (
    select
        common.ar province_id,
        common.ba brand,
        common.ch channel,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and common.uid is not null
     )t1
    group by user_id
)log
on ui.user_id=log.user_id;
"


dwd_interaction_favor_add_inc="
insert overwrite table ${APP}.dwd_interaction_favor_add_inc partition(dt='$do_date')
select
    data.id,
    data.user_id,
    data.sku_id,
    date_format(data.create_time,'yyyy-MM-dd') create_time,
    date_format(data.update_time,'yyyy-MM-dd') update_time,
    data.create_time
from ${APP}.ods_favor_info_inc
where dt='$do_date'
and type = 'bootstrap-insert';
"


dwd_course_chapter_play_inc="
with
play as(
  select appvideo.play_sec play_sec,
         appvideo.position_sec position_sec,
         appvideo.video_id video_id,
         common.uid user_id,
         date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') create_time,
         date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') tt
  from ${APP}.ods_log_inc
  where appvideo is not null 
  and dt='$do_date'
),
video as (
  select data.id,
         data.during_sec,
         data.chapter_id,
         data.course_id
  from ${APP}.ods_video_info_inc
  where type='bootstrap-insert'
     or type='insert'
)
insert overwrite table ${APP}.dwd_course_chapter_play_inc partition(dt='$do_date')
select video.chapter_id chapter_id,
       video.id video_id,
       video.course_id course_id,
       play.user_id user_id,
       play.play_sec play_sec,
       play.position_sec position_sec,
       video.during_sec during_sec,
       create_time
from play
left join video on play.video_id=video.id
where tt='$do_date';
"

dwd_course_chapter_process_inc="
insert overwrite table ${APP}.dwd_course_chapter_process_inc partition(dt)
select data.id,
       data.course_id,
       data.chapter_id,
       data.user_id,
       data.position_sec,
       data.create_time ,
       data.update_time,
       data.deleted,
       date_format(data.create_time,'yyyy-MM-dd') dt
from ${APP}.ods_user_chapter_process_inc
where dt='$do_date' 
and type='bootstrap-insert';
"


dwd_course_exam_inc="
with
exam as (
  select data.id           id,
         data.paper_id     paper_id,
         data.user_id      user_id,
         data.score        score,
         data.duration_sec duration_sec,
         data.create_time  create_time
  from ${APP}.ods_test_exam_inc
  where dt = '$do_date'
    and type = 'bootstrap-insert'
),
eq as (
  select data.exam_id     exam_id,
         data.paper_id    paper_id,
         data.question_id question_id,
         data.is_correct  is_correct
  from ${APP}.ods_test_exam_question_inc
  where dt = '$do_date'
    and type = 'bootstrap-insert'
),
tp as (
  select data.id        paper_id,
         data.course_id course_id
  from ${APP}.ods_test_paper_inc
  where type = 'bootstrap-insert'
)
insert overwrite table ${APP}.dwd_course_exam_inc partition(dt)
select id,
       exam_id,
       exam.paper_id,
       user_id,
       score,
       question_id,
       is_correct,
       course_id,
       duration_sec,
       create_time text_time,
       date_format(create_time,'yyyy-MM-dd') dt
from exam
left join eq on exam.paper_id=eq.paper_id
left join tp on exam.paper_id=tp.paper_id;
"


dwd_course_review_inc="
insert overwrite table ${APP}.dwd_course_review_inc partition(dt)
select data.id,
       data.user_id,
       data.course_id,
       data.review_txt,
       data.review_stars,
       data.create_time,
       data.deleted,
       date_format(data.create_time,'yyyy-MM-dd') dt
from ${APP}.ods_review_info_inc
where dt='$do_date' 
and type='bootstrap-insert';
"


case $1 in
    "dwd_trade_cart_add_inc" )
        hive -e "$dwd_trade_cart_add_inc"
    ;;
    "dwd_trade_order_detail_inc" )
        hive -e "$dwd_trade_order_detail_inc"
    ;;
    "dwd_trade_pay_detail_suc_inc" )
        hive -e "$dwd_trade_pay_detail_suc_inc"
    ;;
    "dwd_traffic_page_view_inc" )
        hive -e "$dwd_traffic_page_view_inc"
    ;;
    "dwd_traffic_display_inc" )
        hive -e "$dwd_traffic_display_inc"
    ;;
    "dwd_traffic_action_inc" )
        hive -e "$dwd_traffic_action_inc"
    ;;
    "dwd_traffic_start_inc" )
        hive -e "$dwd_traffic_start_inc"
    ;;
    "dwd_user_login_inc" )
        hive -e "$dwd_user_login_inc"
    ;;
    "dwd_traffic_error_inc" )
        hive -e "$dwd_traffic_error_inc"
    ;;
    "dwd_user_register_inc" )
        hive -e "$dwd_user_register_inc"
    ;;
    "dwd_interaction_favor_add_inc" )
        hive -e "$dwd_interaction_favor_add_inc"
    ;;
    "dwd_course_chapter_play_inc" )
        hive -e "$dwd_course_chapter_play_inc"
    ;;
    "dwd_course_chapter_process_inc" )
        hive -e "$dwd_course_chapter_process_inc"
    ;;
    "dwd_course_exam_inc" )
        hive -e "$dwd_course_exam_inc"
    ;;
    "dwd_course_review_inc" )
        hive -e "$dwd_course_review_inc"
    ;;
    "all" )
        hive -e "$dwd_trade_cart_add_inc$dwd_trade_order_detail_inc
        $dwd_trade_pay_detail_suc_inc$dwd_traffic_page_view_inc
        $dwd_traffic_display_inc$dwd_traffic_action_inc
        $dwd_traffic_start_inc$dwd_user_login_inc
        $dwd_traffic_error_inc$dwd_user_register_inc
        $dwd_interaction_favor_add_inc$dwd_course_chapter_play_inc
        $dwd_course_chapter_process_inc$dwd_course_exam_inc
        $dwd_course_review_inc"
esac